package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.io.File;
import java.io.InputStream;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyzzImportservice;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzAzWxRyZs;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzJsRy;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzAzWxRyZsService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzEquipmentService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzInfoService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzOtherEvidenceService;
import com.augurit.gzcgwsp.qyzz.web.form.YwRqQyzzInfoForm;
import com.augurit.gzcgwsp.ywcmn.entity.YwComSjcllist;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjclService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjcllistService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjclForm;


/**
 * excel导入
 * 
 * @author xgw
 * 
 */
@Service
@Transactional
public class YwRqQyjyzzImportserviceImpl implements IYwRqQyjyzzImportservice{
	private static final Log log=LogFactory.getLog(YwRqQyjyzzImportserviceImpl.class);
	@Autowired
	private IYwRqQyzzInfoService ywRqQyzzInfoService;
	@Autowired
	private IYwRqQyzzJsRyService ywRqQyzzJsRyService;
	@Autowired
	private IYwRqQyzzAzWxRyZsService ywRqQyzzAzWxRyZsService;
	@Autowired
	private IYwRqQyzzEquipmentService ywRqQyzzEquipmentService;
	@Autowired
	private IYwRqQyzzOtherEvidenceService ywRqQyzzOtherEvidenceService;
	@Autowired
	private IYwComSjclService ywComSjclService;
    @Autowired
    private IYwComSjcllistService ywComSjcllistService;
    @Autowired
    private IYwComQyxxService ywComQyxxService;
	/* 
	 * excel导入
	 * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyzzImportservice#importExcel(long, long, java.lang.String, java.lang.String, java.lang.String, java.io.InputStream)
	 */
    public Map<String,String> importExcel(long rqjyxkid,long qyid, String acceptNo, String username,
            Map<String,String> params, InputStream uploadFile) throws Exception{
        Map<String,String> msgMap=new HashMap<String,String>();
        String error="";
        msgMap.put("id", String.valueOf(rqjyxkid));
        msgMap.put("qyid", String.valueOf(qyid));
        msgMap.put("acceptNo", acceptNo);
    	WritableWorkbook wwb;
    	Workbook wb=ExcelUtil.getWorkBookByInputStream(uploadFile);
    	Sheet[] sheets=wb.getSheets();
       if(sheets.length!=6){
           error="上传的模板文件格式错误\n";
           msgMap.put("error", error);  
           return msgMap;
       }
		Sheet sheet=sheets[1];
		Sheet sheet2=sheets[2];
		Sheet sheet3=sheets[3];
		Sheet sheet4=sheets[4];
		Sheet sheet5=sheets[5];
		//导入第一个工作表的内容
		 error+=ywRqQyzzInfoService.importQyzzInfo(sheet,acceptNo, qyid,username,params);
		//第二个工作表
         error+=ywRqQyzzJsRyService.importQyzzJsRy(sheet2, acceptNo, qyid, username,params);
		//第三个工作表
		error+=ywRqQyzzAzWxRyZsService.importAZWxRyZs(sheet3, acceptNo, qyid, username,params);
		//第四个工作表
		error+=ywRqQyzzEquipmentService.importQyzzEquipment(sheet4, acceptNo, qyid, username,params);
		//第五个工作表
		error+=ywRqQyzzOtherEvidenceService.importQyzzOtherEvidence(sheet5, acceptNo, qyid, username,params);
    	return msgMap;
    }



    
    /**
     * 导出excel
     */
	public String exportExcel(long qyid, String acceptNo, String username,
			String params, String path,String type) throws Exception {
	    String inFilePath = path+File.separator+"Templates"+File.separator+"azwxqyzz.xls";
	    if("qyxx".equals(type)){
	        inFilePath= path+File.separator+"Templates"+File.separator+"viewqyzz.xls";
	    }
		String outpathDirectory=path+File.separator+"Templates"+File.separator+"modelTemplate";
		String outFilePath = GetFileName.getExcelPath("xls",outpathDirectory);
		outFilePath =writerDBDataToExcel(inFilePath, outFilePath,acceptNo,qyid,type);
		
		return outFilePath;
	}

	/**
	 * 开始写文件
	 * @param inFilePath
	 * @param outFilePath
	 * @return
	 */
	private String writerDBDataToExcel(String inFilePath, String outFilePath,String acceptNo,long qyid,String type){
		WritableWorkbook wwb;
		try {
		    if("qyxx".equals(type)){
		        wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
	            WritableSheet wsTwo = ExcelUtil.getWritableSheetByIndex(wwb, 0);
	            WritableSheet wsThree = ExcelUtil.getWritableSheetByIndex(wwb, 1);
	            WritableSheet wsFour = ExcelUtil.getWritableSheetByIndex(wwb, 2);
	            WritableSheet wsFive = ExcelUtil.getWritableSheetByIndex(wwb, 3);
	            WritableSheet wsSix = ExcelUtil.getWritableSheetByIndex(wwb, 4);
	            ywRqQyzzInfoService.copyWsTwo(wsTwo,acceptNo,qyid,type);
	            ywRqQyzzJsRyService.copyWritableSheet(wsThree,acceptNo,  qyid,type);
	            ywRqQyzzAzWxRyZsService.copyWritableSheet(wsFour,acceptNo,  qyid,type);
	            ywRqQyzzEquipmentService.copyWritableSheet(wsFive,acceptNo,  qyid,type);
	            ywRqQyzzOtherEvidenceService.copyWritableSheet(wsSix,acceptNo,  qyid,type);
		    }else{
		        wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
	            WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
	            WritableSheet wsTwo = ExcelUtil.getWritableSheetByIndex(wwb, 1);
	            WritableSheet wsThree = ExcelUtil.getWritableSheetByIndex(wwb, 2);
	            WritableSheet wsFour = ExcelUtil.getWritableSheetByIndex(wwb, 3);
	            WritableSheet wsFive = ExcelUtil.getWritableSheetByIndex(wwb, 4);
	            WritableSheet wsSix = ExcelUtil.getWritableSheetByIndex(wwb, 5);
	            ywRqQyzzInfoService.copyWsOne(wsOne,acceptNo,qyid);
	            ywRqQyzzInfoService.copyWsTwo(wsTwo,acceptNo,qyid,type);
	            ywRqQyzzJsRyService.copyWritableSheet(wsThree,acceptNo,  qyid,type);
	            ywRqQyzzAzWxRyZsService.copyWritableSheet(wsFour,acceptNo,  qyid,type);
	            ywRqQyzzEquipmentService.copyWritableSheet(wsFive,acceptNo,  qyid,type);
	            ywRqQyzzOtherEvidenceService.copyWritableSheet(wsSix,acceptNo,  qyid,type);
		    }
			
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			log.error("受理号"+acceptNo+"写文件错误"+e);
		} 
		
		return outFilePath;
	}
/*
 *  经办人结案环节检查到数据不一致时候再导入
 * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyzzImportservice#reImportExcel(java.util.Map)
 */
    public Map<String, String> reImportExcel(Map map) {
        String process=(String) map.get("process");
        String filepath=(String) map.get("tempFilePath");
        String isCover=(String) map.get("isCover");
        String sheetIndex=(String) map.get("sheetIndex");
        String colIndex=(String) map.get("colIndex");
        File file=new File(filepath);
        Workbook wb;
        try {
            wb = Workbook.getWorkbook(file);
            Sheet[] sheets=wb.getSheets();
            
            
            
        } catch (Exception e) {
            log.info("再导入失败"+e);
        } 
        return null;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyzzImportservice#importFinalData(java.util.Map)
     */
    public String importFinalData(Map map) {
        String id=(String) map.get("id");
        String qyid=(String) map.get("qyid");
        String templateCode=(String) map.get("templateCode");
        String filepath=(String) map.get("contextPath");
        String acceptNo=(String) map.get("acceptNo");
        //map.put("process", "cksa");//窗口收案的标志
        String error="";
        Workbook wb;
        //查询材料模版
        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
        Long tempId=0l;
        for(YwComSjcllist entity:sjcllists){
            String title=entity.getTitle();
            if(title.indexOf("申请表")>=0){
                tempId=entity.getId();
            }
        }
        //查询上传的材料
        List<YwComSjclForm> sjclTpl = getFiles(Long.valueOf(qyid), Long.valueOf(id), templateCode);
         if(!hasFile(tempId,sjclTpl)){
            error="导入失败,上传的收件材料没有正确的文件\n";
            return error;
        }
        for(YwComSjclForm entity:sjclTpl){
            try {
                if(String.valueOf(tempId).equals(String.valueOf(entity.getTplid()))){
                    filepath+=entity.getFilepath();
                    filepath=filepath.replace("/", File.separator);
                    File file=new File(filepath);
                    String filename=entity.getFilename();
                    filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                    if(!file.exists()){
                        continue;
                    }else{
                        wb = Workbook.getWorkbook(file);
                        if(isCorrectFile(wb)){
                            Sheet[] sheets=wb.getSheets();
                            //第一个工作表
                            error+=ywRqQyzzInfoService.importQyzzInfo(sheets[1],acceptNo, Long.valueOf(qyid),"",map);
                            //第二个工作表
                            error+=ywRqQyzzJsRyService.importQyzzJsRy(sheets[2], acceptNo, Long.valueOf(qyid), "",map);
                           //第三个工作表
                           error+=ywRqQyzzAzWxRyZsService.importAZWxRyZs(sheets[3], acceptNo, Long.valueOf(qyid), "",map);
                           //第四个工作表
                           error+=ywRqQyzzEquipmentService.importQyzzEquipment(sheets[4], acceptNo, Long.valueOf(qyid), "",map);
                           //第五个工作表
                           error+=ywRqQyzzOtherEvidenceService.importQyzzOtherEvidence(sheets[5], acceptNo, Long.valueOf(qyid),"",map);
                           break;
                        }
                   }
              }
            } catch (Exception e) {
                log.error("导入失败,系统出现错误"+e);
                error="导入失败,系统出现错误\n";
            }
        }
    
        return error;
    }
    /**
     * 是否存在文件
     * @return
     */
    private boolean hasFile(Long tempId, List<YwComSjclForm> list){
        String filenames="";
        if(list!=null && list.size()>0){
            for(YwComSjclForm entity:list){
                    if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                        String filename=entity.getFilename();
                        if(StringUtils.isNotBlank(filename)){
                            filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                            if("xls".equals(filename)){
                                filenames=filename;
                                break;
                            }
                        }
                    }
               }
            }
           if(StringUtils.isNotBlank(filenames))  return true;
        
        return false;
    }
    
    /**
     * 获取收件材料
     * @param qyid
     * @param ajid
     * @param templateCode
     * @return
     */
    private List<YwComSjclForm> getFiles(Long qyid,Long ajid,String templateCode){
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(qyid);
        fkForm.setAjid(ajid);
        fkForm.setTplcode(templateCode);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        return sjclTpl;
    }
    
    
    /**
     * 是否是正确的文件格式
     * @param wb
     * @return
     */
    private boolean isCorrectFile(Workbook wb){
        Sheet[] sheets=wb.getSheets();
        if(sheets.length==6)
            return true;
        else
            return false;
    }




    /**
     * 
     */
    public Map importFinalQyData(YwRqQyzzInfoForm form) {
        String acceptNo=form.getAcceptNo();
        Long qyid=form.getQyid();
        //基本信息
        ywRqQyzzInfoService.importFinalData(form);
        //企业专业技术人员状况表
        ywRqQyzzJsRyService.importFinalData(qyid,acceptNo);
        //燃气燃烧器具安装维修人员持证情况
        ywRqQyzzAzWxRyZsService.importFinalData(qyid,acceptNo);
        //企业主要设备与检测设备
        ywRqQyzzEquipmentService.importFinalData(qyid,acceptNo);
        //其它证明材料
        ywRqQyzzOtherEvidenceService.importFinalData(qyid,acceptNo);
        
        return null;
    }




    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyzzImportservice#checkImportPerson(java.util.Map)
     */
	public String checkImportPerson(Map map) {
		  String id=(String) map.get("id");
	        String qyid=(String) map.get("qyid");
	        String templateCode=(String) map.get("templateCode");
	        String filepath=(String) map.get("contextPath");
	        String error="";
	        Workbook wb;
	        //查询材料模版
	        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
	        Long tempId=0l;
	        for(YwComSjcllist entity:sjcllists){
	            String title=entity.getTitle();
	            if(title.indexOf("申请表")>=0){
	                tempId=entity.getId();
	            }
	        }
	        //查询上传的材料
	        List<YwComSjclForm> sjclTpl = getFiles(Long.valueOf(qyid), Long.valueOf(id), templateCode);
	        for(YwComSjclForm entity:sjclTpl){
	            try {
	                if(String.valueOf(tempId).equals(String.valueOf(entity.getTplid()))){
	                    filepath+=entity.getFilepath();
	                    filepath=filepath.replace("/", File.separator);
	                    File file=new File(filepath);
	                    String filename=entity.getFilename();
	                    filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
	                    if(!file.exists()){
	                        continue;
	                    }else{
	                        wb = Workbook.getWorkbook(file);
	                        if(isCorrectFile(wb)){
	                        	Map<Long,String> qymap=ywComQyxxService.getQyxx("'rqrsqjazwxqyzz'");
	                            Sheet[] sheets=wb.getSheets();
	                            Sheet sheet=sheets[2];
	                            Sheet sheet3=sheets[3];
	                            for(int i=2;i<sheet.getRows();i++){
	                    			String name=ExcelUtil.readData(sheet, 0, i);//姓名
	                    			String idCard=ExcelUtil.readData(sheet, 1, i);//身份证号码
	                    			if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(idCard)){
	                    				List<Long> list=ywRqQyzzJsRyService.getPersons(name,idCard,Long.valueOf(qyid));
	                    				 if(list!=null && list.size()>0){
	                						 String qyname="";
	                						 Set<Long> set=new HashSet<Long>();
	                						 for(Long idstr:list){
	                							 set.add(idstr);
	                						 }
	                						 for(Long idstr:set){
	                							 if(StringUtils.isNotBlank(qymap.get(idstr))
	                							    &&StringUtils.isNotBlank(qymap.get(qyid)) &&!qymap.get(idstr).equals(qymap.get(qyid))){
	                								 qyname+=qymap.get(idstr)==null?"":qymap.get(id)+",";
	                							 }
	                						 }
	                						 if(StringUtils.isNotBlank(qyname))
	                							 qyname=qyname.substring(0,qyname.length()-1);
	                						     error+="从业人员状况登记表第"+i+"行姓名："+name+",身份证号码:"+idCard+"在其他企业存在:"+qyname+"##";
	                					 }
	                    			}
	                    		}
	                            
	                        
	                   	    for(int j=2;j<sheet3.getRows();j++){
	                	        String name=ExcelUtil.readData(sheet3, 1, j);//姓名
	                	        String idCard=ExcelUtil.readData(sheet3, 2, j);//身份证号码
	                	        if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(idCard)){
	                	        	List<Long> qyids=ywRqQyzzAzWxRyZsService.getPersonData(name,idCard,Long.valueOf(qyid));
	                	        	if(qyids!=null && qyids.size()>0){
                   					 String qyname="";
	                   					for(Long qyId:qyids){
	                   							 qyname+=qymap.get(qyId)+",";
	                   						    if(StringUtils.isNotBlank(qyname))
	                   							 qyname=qyname.substring(0,qyname.length()-1); 
	                   					}
	                   				  error+="燃气燃烧器具安装维修人员持证情况第"+j+"行姓名"+name+",身份证号码"+idCard+"在其他企业存在:"+qyname+"##";
                   				  }
	                	        }
	                	    } 
	                           break;
	                        }
	                   }
	              }
	            } catch (Exception e) {
	                log.error("导入失败,系统出现错误"+e);
	                error="导入失败,系统出现错误\n";
	            }
	        }
	        return error;
		
	}

}
